
import sqlalchemy
from sqlalchemy import Column,Integer,String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
class Transcript(Base):                                    # 新建一个实体类
    __tablename__ = 'transcript'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(14), nullable=False)
    chinese = Column(Integer, nullable=False)
    math = Column(Integer, nullable=False)
    english = Column(Integer, nullable=False)

    def __repr__(self):
        return "{} id={} chinese={} math={} english={}".format(self.__class__.__name__, self.id,
                                                               self.chinese, self.math, self.english)

engine = sqlalchemy.create_engine("mysql+pymysql://lin:lin@192.168.43.200:3306/mysql")
Base.metadata.create_all(engine)                          # 新建一个连接
Session = sessionmaker(bind=engine)
session = Session()                                       # 新建一个会话

def add_data(data:list):                              # 成绩输入方法
    try:
        transcript = Transcript(name=data[0], chinese=int(data[1]), math=int(data[2]), english=int(data[3]))
        session.add(transcript)
        session.commit()
        print('成绩录入成功')
    except Exception as e:
        print('成绩录入失败')
        session.rollback()
        print(e)

def query_data(name:str):                            # 成绩查询方法
    tmp = session.query(Transcript).filter(Transcript.name == name)
    if tmp.all() == []:
        return '没有找到该学生的成绩'
    return tmp.all()



